--    Program: 		CAPARIMP_APPROVAL_LIMIT.sql
--    Description:  Import AR Receipt Source information 
--    Revision:     1st     
--    Parameter: 	

set doc off;      
set verify off;
set serveroutput on size 1000000;
--whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
                      		                 		
   cursor cur_limit is
      select * from CRST_AR_APPROVAL_USER_LIMITS
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_limit loop  
   	 l_err_msg_tmp := '';
   	 
   	 update CRST_AR_APPROVAL_USER_LIMITS
   	 set    user_id_n = CAPBRIMP_MAPPING.GetUserMapping(cur.USER_ID)
   	 WHERE CURRENT OF cur_limit;
   	 
   	 begin 
   	   -- import data
       insert into AR_APPROVAL_USER_LIMITS 
       (  
         USER_ID             ,
         CREATED_BY          ,
         CREATION_DATE       ,
         LAST_UPDATE_DATE    ,
         LAST_UPDATED_BY     ,
         LAST_UPDATE_LOGIN   ,
         AMOUNT_TO           ,
         AMOUNT_FROM         ,
         ATTRIBUTE1          ,
         ATTRIBUTE2          ,
         ATTRIBUTE3          ,
         ATTRIBUTE4          ,
         ATTRIBUTE5          ,
         ATTRIBUTE6          ,
         ATTRIBUTE7          ,
         ATTRIBUTE8          ,
         ATTRIBUTE9          ,
         ATTRIBUTE10         ,
         ATTRIBUTE_CATEGORY  ,
         ATTRIBUTE11         ,
         ATTRIBUTE12         ,
         ATTRIBUTE13         ,
         ATTRIBUTE14         ,
         ATTRIBUTE15         ,
         CURRENCY_CODE       ,
         DOCUMENT_TYPE       ,
         REASON_CODE         ,
         PRIMARY_FLAG
       )
       values
       (
         CAPBRIMP_MAPPING.GetUserMapping(cur.USER_ID),
         CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
         cur.CREATION_DATE       ,
         cur.LAST_UPDATE_DATE    ,
         CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
         cur.LAST_UPDATE_LOGIN   ,
         cur.AMOUNT_TO           ,
         cur.AMOUNT_FROM         ,
         cur.ATTRIBUTE1          ,
         cur.ATTRIBUTE2          ,
         cur.ATTRIBUTE3          ,
         cur.ATTRIBUTE4          ,
         cur.ATTRIBUTE5          ,
         cur.ATTRIBUTE6          ,
         cur.ATTRIBUTE7          ,
         cur.ATTRIBUTE8          ,
         cur.ATTRIBUTE9          ,
         cur.ATTRIBUTE10         ,
         cur.ATTRIBUTE_CATEGORY  ,
         cur.ATTRIBUTE11         ,
         cur.ATTRIBUTE12         ,
         cur.ATTRIBUTE13         ,
         cur.ATTRIBUTE14         ,
         cur.ATTRIBUTE15         ,
         cur.CURRENCY_CODE       ,
         cur.DOCUMENT_TYPE       ,
         cur.REASON_CODE         ,
         cur.PRIMARY_FLAG
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp = '' then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;  	  
   end loop;
   
   if l_rejected_count <> 0 then
   	 commit;
   	 
   	 update CAR_APPROVAL_USER_LIMITS ul
   	 set    s_org_id = CAPBRIMP_MAPPING.GetOrgMapping(s_org_id),
   	        s_business_group_id = CAPBRIMP_MAPPING.GetBGMapping(s_business_group_id)
   	 where  exists (select 'X'
   	                from   CRST_AR_APPROVAL_USER_LIMITS cul
   	                where  cul.user_id_n = ul.user_id);
   	 
   	 commit;
   else
     rollback;
   end if;     
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
   values ('CAPARIMP_APPROVAL_LIMIT', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit